from flask import Blueprint,request,jsonify
from models.House import House
from flask import render_template
from utils.location import location
from  settiings import  Config
from sqlalchemy import  func


bp = Blueprint('index',__name__,url_prefix='/')

@bp.route('/')
def index():
    city = location(Config.LOCATION_AK).get('city')
    count = House.query.count() #房源总数
    #时间戳
    house_new_list = House.query.filter_by(region='岳麓区').order_by(House.publish_time.desc()).limit(6).all()
    # house_new_list = House.query.filter_by(area=city).order_by(House.publish_time.desc()).limit(6).all()
    house_hot_list = House.query.order_by(House.page_views.desc()).limit(4).all()
    return render_template('index.html',
                              count=count,
                              city=city,
                              house_new_list=house_new_list,
                              house_hot_list=house_hot_list)


#搜索
@bp.route('/search/keyword/', methods = ['POST'])
def search_kw():
    kw = request.form['kw']
    info = request.form['info']
    if info == '地区搜索':
        """
        SELECT address, COUNT(address) AS num_addr FROM house_info
        WHERE address LIKE '%朝阳%'
        GROUP BY address
        ORDER BY num_addr DESC
        LIMIT 9;
        """

        result = (House.query.with_entities(House.address,func.count(House.address))  # select
                  .filter(House.address.contains(kw))  # where
                  .group_by(House.address) #
                  .order_by(func.count(House.address).desc()) #desc降序
                  .limit(9).all())
        if len(result):#有查询结果
            array = []
            for i in result:
                array.append({'t_name':i[0],'num':i[1]})
            return jsonify({'code':1,'info':array})
        else: #没有查询到结果
            return jsonify({'code':0,'info':[]})

    elif info == '户型搜索':
        result = (House.query.with_entities(House.rooms, func.count(House.rooms))
                  .filter(House.rooms.contains(kw))
                  .group_by(House.rooms)
                  .order_by(func.count(House.rooms).desc())  # desc降序
                  .limit(9).all())
        if len(result):  # 有查询结果
            array = []
            for i in result:
                array.append({'t_name': i[0], 'num': i[1]})
            return jsonify({'code': 1, 'info': array})
        else:  # 没有查询到结果
            return jsonify({'code': 0, 'info': []})


@bp.route('/list_1')
def list_1():
    house = House.query.first()
    print(house)
    return "查询完毕，请查看后端控制台输出"